package org.clipjoint.dao.impl;


import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.lang3.StringUtils;
import org.clipjoint.dao.CommentAdminDao;
import org.clipjoint.entity.dto.CommentDto;
import org.clipjoint.entity.Comment;
import org.clipjoint.util.DataSourceUtil;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class CommentAdminDaoImpl implements CommentAdminDao {
    @Override
    //查询全部数据
    public List<Comment> selectAll(CommentDto commentDto) throws SQLException {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
        //动态sql
        String sql="select `comment`.* ,`game`.name ,`user`.username from `comment`,`game`,`user` where `comment`.game_id=`game`.id and `comment`.user_id=`user`.id";
        List<Object> params=new ArrayList<>();
        //判断条件查询
        if (StringUtils.isNotBlank(commentDto.getName())){
            sql+=" and `game`.name =?";
            params.add(commentDto.getName());
        }
        if (commentDto.getCreateTimeStart()!=null&&commentDto.getCreateTimeEnd()!=null){
            sql+=" and `comment`.create_time>= ? and `comment`.create_time<=?";
            params.add(commentDto.getCreateTimeStart());
            params.add(commentDto.getCreateTimeEnd());
        }

        //判断排序参数
        if(StringUtils.isNoneBlank(commentDto.getOrderField(),commentDto.getOrderType())){
            sql+=" order by `comment`."+ commentDto.getOrderField()+" "+commentDto.getOrderType();
        }
        //分页参数
        if(commentDto.getPage()!=null &&commentDto.getLimit()!=null){
            sql+=" limit ?,?";
            params.add((commentDto.getPage()-1)*commentDto.getLimit());
            params.add(commentDto.getLimit());

        }

        //查询所有结果
        List<Comment> result=qr.query(sql,new BeanListHandler<>(Comment.class),params.toArray());
        return result;
    }

    @Override
    //统计数量
    public Long selectCount(CommentDto commentDto) throws SQLException {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
        //动态sql
        String sql="select count(*) from `comment` ,`game` where `comment`.user_id=`game`.id";
        List<Object> params=new ArrayList<>();
        //判断条件查询
        if (StringUtils.isNotBlank(commentDto.getName())){
            sql+=" and `game`.name =?";
            params.add(commentDto.getName());
        }
        if (commentDto.getCreateTimeStart()!=null&&commentDto.getCreateTimeEnd()!=null){
            sql+=" and `comment`.create_time>= ? and `comment`.create_time<=?";
            params.add(commentDto.getCreateTimeStart());
            params.add(commentDto.getCreateTimeEnd());
        }
        //查询一个结果
        Map<String, Object> result = qr.query(sql, new MapHandler(), params.toArray());
        return (Long) result.get("count(*)");
    }


    @Override
    //删除一条数据
    public int deleteById(Long id) throws SQLException {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
        String sql="delete from `comment` where `id`=?";
        int result=qr.update(sql,id);
        return result;
    }

    @Override
    public Comment selectOne(CommentDto commentDto) throws SQLException {
        //动态sql
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
        //动态sql
        String sql="select `comment`.* ,`game`.name ,`user`.username from `comment`,`game`,`user` where `comment`.game_id=`game`.id and `comment`.user_id=`user`.id";
        List<Object> params=new ArrayList<>();
        //判断条件查询
        if (StringUtils.isNotBlank(commentDto.getName())){
            sql+=" and `game`.name =?";
            params.add(commentDto.getName());
        }


        //查询一个结果
        Comment result=qr.query(sql,new BeanHandler<>(Comment.class),params.toArray());
        return result;
    }

    @Override
    public List<Comment> show(Long gameid) throws SQLException {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
        String sql="SELECT `comment`.content,`comment`.create_time,`user`.username from `comment`,`user` WHERE `game_id`= ? AND `user`.id = any(SELECT user_id FROM `comment` WHERE game_id = ?)";
        List<Comment> result = qr.query(sql,new BeanListHandler<>(Comment.class),gameid,gameid);
        return  result;
    }

    @Override
    public int add(Comment comment) throws SQLException {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
        String sql="insert into `comment` (`game_id`,`user_id`,`content`,`create_time`) value( ? , ? , ? , ? )";
        int result=qr.update(sql,comment.getGame_id(),comment.getUser_id(),comment.getContent(),comment.getCreate_time());
        return result;
    }


}
